/*
 * 
 * Version 1.00
 * 
 * Created on 2007-6-4
 * Encoding GB2312
 */
package net.comm.message.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import java.util.Vector;

import net.comm.check.service.impl.ChecklogServiceImpl;
import net.comm.check.web.form.ChecklogForm;
import net.comm.common.GlbFinal;
import net.comm.common.Message;
import net.comm.common.ReturnMessage;
import net.comm.common.common;
import net.comm.friend.service.impl.FriendServiceImpl;
import net.comm.friend.service.impl.LinjuServiceImpl;
import net.comm.friend.web.form.FriendForm;
import net.comm.friend.web.form.LinjuForm;



import net.comm.message.bean.BaseMessage;
import net.comm.message.bean.BaseMsgcont;
import net.comm.message.db.MessageServiceSqlMysql;
import net.comm.message.db.DbMessageService;
import net.comm.message.db.MsgcontServiceSqlMysql;
import net.comm.message.web.form.MessageForm;
import net.comm.message.web.form.MsgcontForm;
import net.comm.page.PageCtr;
import net.comm.param.service.impl.SequenceMgr;
import net.comm.shequ.db.DbShequService;
import net.comm.shequ.db.ShequServiceSqlMysql;
import net.comm.shequ.web.form.ShequForm;
import net.comm.user.db.ContactsServiceSqlMysql;
import net.comm.user.db.DbContactsService;
import net.comm.user.service.impl.UserSetServiceImpl;
import net.comm.user.service.impl.UserSysinfoServiceImpl;
import net.comm.user.web.form.UserSetForm;
import net.comm.user.web.form.UserSysinfoForm;
import net.comm.util.DatetimeUtil;
import net.comm.util.DbcpPool;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;
import org.sakaiproject.db.impl.BasicSqlService;


/**
 *
 * @author hcgong(hcgong@sina.com)
 * @version 1.0
 * @date 2007-6-4
 */
public class MessageServiceImpl {

	static Logger logger = Logger.getLogger(MessageServiceImpl.class.getName());

	/**
	 * 
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#list(net.swift.module.other.ExaQueryBean, net.swift.common.ReturnMessage)
	 */
	public List list(MessageForm theForm,  ReturnMessage msg) 
	{
		List list = null;
		
		Connection cn = null;

		try
		{
			cn = DbcpPool.getConnection();
			
			MessageServiceSqlMysql servSql = new MessageServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();

			/*
			 * 查收件箱
			 */
			if(theForm.getQryReceiver() > 0){

				Object[] fields2 = new Object[2];
				fields2[0] = theForm.getQryReceiver();			
				fields2[1] = GlbFinal.DELMSG_NO;			
				
				String sql = servSql.getCountByReceiverSql();
				
				/**
				 * 根据SQL执行结果计算分页相关信息
				 */
				PageCtr page = theForm.getPage();
				page.setTotalNum(bSqlServ.dbCount(cn, sql, fields2));
				page.caculate();				

				Object[] fields = new Object[4];
				fields[0] = theForm.getQryReceiver();			
				fields[1] = GlbFinal.DELMSG_NO;			
				fields[2] = page.getPos()-1;
				fields[3] = page.getPageSize();			
				
				sql = servSql.getRecReceiverbyPageSql();
				
				list = bSqlServ.dbRead(cn, sql, fields, new DbMessageService());
				
			}
			//查发件箱
			else if(theForm.getQrySender() > 0){
				
				Object[] fields2 = new Object[2];
				fields2[0] = theForm.getQrySender();			
				fields2[1] = GlbFinal.DELMSG_NO;			
				
				String sql = servSql.getCountBySenderSql();
				
				/**
				 * 根据SQL执行结果计算分页相关信息
				 */
				PageCtr page = theForm.getPage();
				page.setTotalNum(bSqlServ.dbCount(cn, sql, fields2));
				page.caculate();				

				Object[] fields = new Object[4];
				fields[0] = theForm.getQrySender();			
				fields[1] = GlbFinal.DELMSG_NO;			
				fields[2] = page.getPos()-1;			
				fields[3] = page.getPageSize();			
				
				sql = servSql.getRecSenderbyPageSql();
				
				list = bSqlServ.dbRead(cn, sql, fields, new DbMessageService());
			}
			//查通知
			else if(!theForm.getQrySendertype().equals("")){
				
				Object[] fields2 = new Object[2];
				fields2[0] = theForm.getQrySendertype();			
				fields2[1] = GlbFinal.DELMSG_NO;			
				
				String sql = servSql.getCountBysenderTypeSql();
				
				/**
				 * 根据SQL执行结果计算分页相关信息
				 */
				PageCtr page = theForm.getPage();
				page.setTotalNum(bSqlServ.dbCount(cn, sql, fields2));
				page.caculate();				

				Object[] fields = new Object[4];
				fields[0] = theForm.getQrySendertype();			
				fields[1] = GlbFinal.DELMSG_NO;			
				fields[2] = page.getPos()-1;			
				fields[3] = page.getPageSize();			
				
				sql = servSql.getRecSenderTypebyPageSql();
				
				list = bSqlServ.dbRead(cn, sql, fields, new DbMessageService());	
			}

		    DbcpPool.returnConnection(cn);
		    
		} catch(Exception ex) 
		{
			/**
			 * 记录相关日志
			 */
			logger.error(ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
			
		} finally {
			DbcpPool.returnConnection(cn);
		}
				
		return list;
	}
	
	public List listPrivateMsg(MessageForm theForm,  ReturnMessage msg) 
	{
		List list = null;
		
		Connection cn = null;

		try
		{
			cn = DbcpPool.getConnection();
			
			MessageServiceSqlMysql servSql = new MessageServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();

			/*
			 * 查收件箱
			 */
			if(theForm.getQryReceiver() > 0){

				Object[] fields2 = new Object[2];
				fields2[0] = theForm.getQryReceiver();			
				fields2[1] = GlbFinal.DELMSG_NO;			
				
				String sql = "select count(*) as COUNT from Messages where  receiver=" + fields2[0] + " and recvDelflg=1 and Status=1 and Mtype=1 and Sendertype=1";
				 
				
				/**
				 * 根据SQL执行结果计算分页相关信息
				 */
				PageCtr page = theForm.getPage();
				page.setTotalNum(bSqlServ.dbCount(cn, sql, fields2));
				page.caculate();				

				Object[] fields = new Object[4];
				fields[0] = theForm.getQryReceiver();			
				fields[1] = GlbFinal.DELMSG_NO;			
				fields[2] = page.getPos()-1;
				fields[3] = page.getPageSize();			
				
				StringBuffer sb =  new StringBuffer("select seqID, receiver,  sender,  sendertype, msgID, mtype, " +
						" title,  status,  rTime, recvDelflg,  sndDelflg,  sTime,  mTime " +
						"from Messages");
				
				sb.append(" where Status=1 and Mtype=1 and Sendertype=1 and receiver=? and recvDelflg=? Order by mTime desc ");
				sb.append(" limit ?, ? ");
				sql = sb.toString();
				
				list = bSqlServ.dbRead(cn, sql, fields, new DbMessageService());
				
			}

		    DbcpPool.returnConnection(cn);
		    
		} catch(Exception ex) 
		{
			/**
			 * 记录相关日志
			 */
			logger.error(ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
			
		} finally {
			DbcpPool.returnConnection(cn);
		}
				
		return list;
	}

	/**
	 * 
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#list(net.swift.module.other.ExaQueryBean, net.swift.common.ReturnMessage)
	 */
	public List adlist(MessageForm theForm,  ReturnMessage msg) 
	{
		List list = new Vector();

		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			
			list = adlist( cn,  theForm,   msg); 
			
		    DbcpPool.returnConnection(cn);

		} catch(Exception ex) 
		{
			/**
			 * 记录相关日志
			 */
			logger.error(ex.getMessage());
			try {

				DbcpPool.returnConnection(cn);
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}			
	    }finally {
		
			DbcpPool.returnConnection(cn);
		}		
		return list;
	}
	
	/**
	 * 
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#list(net.swift.module.other.ExaQueryBean, net.swift.common.ReturnMessage)
	 */
	public List adlist(Connection cn, MessageForm theForm,  ReturnMessage msg) 
	{
		List list = new Vector();

		Statement stmt=null;
		ResultSet rs=null;
		try
		{
			
			MessageServiceSqlMysql servSql = new MessageServiceSqlMysql();
			
			StringBuffer sb = new StringBuffer(" where 1=1");

			if(theForm.getQryReceiver() > 0){
				
				sb.append(" and Receiver = ");
				sb.append(theForm.getQryReceiver());
				sb.append(" ");
				
				sb.append(" and RecvDelflg = '");
				sb.append(GlbFinal.DELMSG_NO);
				sb.append("' ");
			}
			if(theForm.getQrySender() > 0){
				
				sb.append(" and Sender = ");
				sb.append(theForm.getQrySender());
				sb.append(" ");
				sb.append(" and SndDelflg = '");
				sb.append(GlbFinal.DELMSG_NO);
				sb.append("' ");
			}		
			if(theForm.getQrySeqID() > 0){
				
				if(theForm.getQryPrevNext().equals(Message.PREV)){
					sb.append(" and SeqID < ");
					sb.append(theForm.getQrySeqID());
					sb.append(" ");
				}else{
					sb.append(" and SeqID >= ");
					sb.append(theForm.getQrySeqID());
					sb.append(" ");
				}
			}		
			String sendertype = theForm.getQrySendertype().replaceAll(GlbFinal.SQLINjection, "");
			if(!sendertype.equals("")){
				
				sb.append(" and Sendertype = '");
				sb.append(sendertype);
				sb.append("' ");
			}
			
			String mtype = theForm.getQryMtype().replaceAll(GlbFinal.SQLINjection, "");
			if(!mtype.equals("")){
				
				sb.append(" and Mtype = '");
				sb.append(mtype);
				sb.append("' ");
			}
			String qryOrderby = theForm.getQryOrderby().replaceAll(GlbFinal.SQLINjection, "");
			if(!qryOrderby.equals("")){
				
				sb.append(" Order by ");
				sb.append(qryOrderby);
				sb.append(" ");
			}else{
				
				sb.append(" Order by Stime desc ");	
			}
			
			//查询记录数
			stmt = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			String  sql = servSql.getCountSql1();

			logger.debug(sql+sb.toString());
			
			rs=stmt.executeQuery(sql+sb.toString());
			int nRows = 0;
			if (rs.next())
	           nRows = rs.getInt("COUNT");
			else
			{
	            rs.close();
	            stmt.close();
	            DbcpPool.returnConnection(cn);
			    return list;
			}
			/**
			 * 根据SQL执行结果计算分页相关信息
			 */
			PageCtr page = theForm.getPage();
			page.setTotalNum(nRows);
			page.caculate();

			sb.append(" limit ");
			sb.append(page.getPos()-1);
			sb.append(", ");
			sb.append(page.getPageSize());
			
			 //取记录
			 sql = servSql.getRecSql();
			 logger.debug(sql+sb.toString());
			 DbMessageService dbblog = new DbMessageService();
			 
			 rs=stmt.executeQuery(sql+sb.toString());
			 while (rs.next())
			 {
				try
				{
					Object obj = dbblog.readSqlResultRecord(rs);
					if (obj != null) list.add(obj);
				}
				catch (Throwable t)
				{
					logger.error(t.getMessage());
				}		
			 }
			 rs.close();
			 stmt.close();

		} catch(Exception ex) 
		{
			/**
			 * 记录相关日志
			 */
			logger.error(ex.getMessage());
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				DbcpPool.returnConnection(cn);
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}			
	    }finally {
		
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}	
		}	
		return list;
	}	
	
	/**
	 * 
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#list(net.swift.module.other.ExaQueryBean, net.swift.common.ReturnMessage)
	 */
	public List detail(MessageForm theForm,  ReturnMessage msg) 
	{
		List list = new Vector();
		
		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();

			if(theForm.getQrySeqID() == 0){
				
				common.setAppFailMsg(msg , " 取记录失败，请检查！");
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return list;
			}		
			
			//取得前一条记录
			theForm.setQryOrderby(" seqID desc ");
			theForm.setQryPrevNext(Message.PREV);
			List  tmplist = adlist(cn, theForm, msg);
			BaseMessage base = null;

			if(tmplist !=  null && tmplist.size() > 0){
				
				base = (BaseMessage)tmplist.get(0);
				list.add(base);
				theForm.setPrevIsExist(GlbFinal.YES);
			}
			//取得本条记录及下一条记录
			theForm.setQryOrderby(" seqID ");
			theForm.setQryPrevNext(Message.NEXT);
			tmplist = adlist(cn, theForm, msg);
			if(tmplist.size() == 0){
				
				common.setAppFailMsg(msg , " 取记录失败，请检查！");
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return list;
			}
			int len = tmplist.size();
			if(len >= 2)
				len = 2;
			for(int i=0; i<len; i++){
				base = (BaseMessage)tmplist.get(i);
				list.add(base);
			}
			if(len > 1){
				theForm.setNextIsExist(GlbFinal.YES);
			}
			
			base = (BaseMessage)tmplist.get(0);
			BeanUtils.copyProperties(theForm, base);
			
			//取得邮件内容
			MsgcontServiceImpl contservice = new MsgcontServiceImpl();
			MsgcontForm msgcontForm = new MsgcontForm();
			
			msgcontForm.setMsgID(base.getMsgID());
			
			contservice.get(cn, msgcontForm, msg);
			
			theForm.setMsg(msgcontForm.getMsg());
			
			//修改邮件状态为已读
			if(base.getStatus().equals(GlbFinal.WEBMSG_UNREAD)){
				
				MessageServiceSqlMysql servSql = new MessageServiceSqlMysql();
				BasicSqlService  bSqlServ = new BasicSqlService();				
				//更新邮件状态为已读
				Object[] fields12 = new Object[13];
				
				fields12[0] = base.getReceiver();
				fields12[1] = base.getSender();
				fields12[2] = base.getSendertype();
				fields12[3] = base.getMsgID();
				fields12[4] = base.getMtype();
				fields12[5] = base.getTitle();
				fields12[6] = GlbFinal.WEBMSG_READ;
				fields12[7] = DatetimeUtil.getDatetime();
				fields12[8] = base.getRecvDelflg();
				fields12[9] = base.getSndDelflg();
				fields12[10] = base.getStime();
				fields12[11] = DatetimeUtil.getDatetime();
				fields12[12] = base.getSeqID();
				
				String sql = servSql.getUpdateSql();
				
				boolean  res = bSqlServ.dbWrite(cn, sql, fields12);
				if(!res){
					common.setAppFailMsg(msg , " 更新记录失败，请检查！");
					DbcpPool.rollBackTran(cn);
					DbcpPool.closeTran(cn);
					DbcpPool.returnConnection(cn);
					return list;
				}	
				
				//更新 新邮件数量
				UserSysinfoServiceImpl syservice  = new UserSysinfoServiceImpl();
				
				UserSysinfoForm sysForm = new UserSysinfoForm();
				sysForm.setUserID(theForm.getQryReceiver());

				sysForm.setNemailNum(-1);
				syservice.update(cn, sysForm, msg);
				if(msg.getErrCode() != 0){
					return list;
				}						
			}			
			
			//更新未读邮件数
			
			
			
			DbcpPool.closeTran(cn);
		    DbcpPool.returnConnection(cn);
		    
		} catch(Exception ex) 
		{
			/**
			 * 记录相关日志
			 */
			logger.error(ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
			
		} finally {
			DbcpPool.returnConnection(cn);
		}
				
		return list;
	}	
	/**
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#get(net.swift.db.tab_paramnostro, net.swift.common.ReturnMessage)
	 */
	public List get(MessageForm theForm, MsgcontForm msgcontForm, ReturnMessage msg) 
	{
		List list = null;
		
		Connection cn = null;

		try
		{
			cn = DbcpPool.getConnection();
			
			DbcpPool.beginTran(cn);
			
			MessageServiceSqlMysql servSql = new MessageServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();

			/*
			 * 查收件箱
			 */
			if(theForm.getQryReceiver() > 0){

				Object[] fields = new Object[4];
				fields[0] = theForm.getQryReceiver();			
				fields[1] = GlbFinal.DELMSG_NO;			
				fields[2] = theForm.getPage().getPos()-1;			
				fields[3] = theForm.getPage().getPageSize();			
				
				String sql = servSql.getRecReceiverbyPageSql();
				
				list = bSqlServ.dbRead(cn, sql, fields, new DbMessageService());
				if(list.size() == 0){
					DbcpPool.rollBackTran(cn);
					DbcpPool.closeTran(cn);
					DbcpPool.returnConnection(cn);
					return list;
				}
	
				//取邮件内容
				BaseMessage base = null;

				if(list.size() == 1 || theForm.getSelect() == 1 && theForm.getPage().getPos() == 1){
					
					base = (BaseMessage)list.get(0);
				}else{
					base = (BaseMessage)list.get(1);
				}
				BeanUtils.copyProperties(theForm, base);

				msgcontForm.setMsgID(base.getMsgID());
				MsgcontServiceImpl contservice = new MsgcontServiceImpl();
				
				contservice.get(cn, msgcontForm, msg);
				
				if(base.getStatus().equals(GlbFinal.WEBMSG_UNREAD)){
					//更新邮件状态为已读
					Object[] fields12 = new Object[13];
					
					fields12[0] = base.getReceiver();
					fields12[1] = base.getSender();
					fields12[2] = base.getSendertype();
					fields12[3] = base.getMsgID();
					fields12[4] = base.getMtype();
					fields12[5] = base.getTitle();
					fields12[6] = GlbFinal.WEBMSG_READ;
					fields12[7] = DatetimeUtil.getDatetime();
					fields12[8] = base.getRecvDelflg();
					fields12[9] = base.getSndDelflg();
					fields12[10] = base.getStime();
					fields12[11] = DatetimeUtil.getDatetime();
					fields12[12] = base.getSeqID();
					
					sql = servSql.getUpdateSql();
					
					boolean  res = bSqlServ.dbWrite(cn, sql, fields12);
					if(!res){
						common.setAppFailMsg(msg , " 更新记录失败，请检查！");
						DbcpPool.rollBackTran(cn);
						DbcpPool.closeTran(cn);
						DbcpPool.returnConnection(cn);
						return list;
					}	
					
					//更新 新邮件数量
					UserSysinfoServiceImpl syservice  = new UserSysinfoServiceImpl();
					
					UserSysinfoForm sysForm = new UserSysinfoForm();
					sysForm.setUserID(theForm.getQryReceiver());

					sysForm.setNemailNum(-1);
					syservice.update(cn, sysForm, msg);
					if(msg.getErrCode() != 0){
						return list;
					}						
				}
				
			}
			//查发件箱
			else if(theForm.getQrySender() > 0){
				

				Object[] fields = new Object[4];
				fields[0] = theForm.getQrySender();			
				fields[1] = GlbFinal.DELMSG_NO;			
				fields[2] = theForm.getPage().getPos()-1;			
				fields[3] = theForm.getPage().getPageSize();			
				
				String sql = servSql.getRecSenderbyPageSql();
				
				list = bSqlServ.dbRead(cn, sql, fields, new DbMessageService());
				if(list.size() == 0){
					common.setAppFailMsg(msg , " 取记录失败，请检查！");
					DbcpPool.rollBackTran(cn);
					DbcpPool.closeTran(cn);
					DbcpPool.returnConnection(cn);
					return list;
				}
				
				//取邮件内容
				BaseMessage base = null;
				if(list.size() == 1 || theForm.getSelect() == 1 && theForm.getPage().getPos() == 1){
						
					base = (BaseMessage)list.get(0);
				}else{
					base = (BaseMessage)list.get(1);
				}
				BeanUtils.copyProperties(theForm, base);
				
				msgcontForm.setMsgID(base.getMsgID());
				MsgcontServiceImpl contservice = new MsgcontServiceImpl();
				
				contservice.get(cn, msgcontForm, msg);
				
			}
			//查通知
			else if(!theForm.getQrySendertype().equals("")){
	
				
				Object[] fields = new Object[4];
				fields[0] = theForm.getQrySendertype();			
				fields[1] = GlbFinal.DELMSG_NO;			
				fields[2] = theForm.getPage().getPos()-1;			
				fields[3] = theForm.getPage().getPageSize();			
				
				String sql = servSql.getRecSenderTypebyPageSql();
				
				list = bSqlServ.dbRead(cn, sql, fields, new DbMessageService());	
				if(list.size() == 0){
					common.setAppFailMsg(msg , " 取记录失败，请检查！");
					DbcpPool.rollBackTran(cn);
					DbcpPool.closeTran(cn);
					DbcpPool.returnConnection(cn);
					return list;
				}

				//取邮件内容
				BaseMessage base = null;
				if(list.size() == 1 || theForm.getSelect() == 1 && theForm.getPage().getPos() == 1){
					
					base = (BaseMessage)list.get(0);
				}else{
					base = (BaseMessage)list.get(1);
				}

				BeanUtils.copyProperties(theForm, base);
				
				msgcontForm.setMsgID(base.getMsgID());
				MsgcontServiceImpl contservice = new MsgcontServiceImpl();
				
				contservice.get(cn, msgcontForm, msg);

				if(base.getStatus().equals(GlbFinal.WEBMSG_UNREAD)){
					
					//更新邮件状态为已读
					Object[] fields12 = new Object[13];
					
					fields12[0] = base.getReceiver();
					fields12[1] = base.getSender();
					fields12[2] = base.getSendertype();
					fields12[3] = base.getMsgID();
					fields12[4] = base.getMtype();
					fields12[5] = base.getTitle();
					fields12[6] = GlbFinal.WEBMSG_READ;
					fields12[7] = DatetimeUtil.getDatetime();
					fields12[8] = base.getRecvDelflg();
					fields12[9] = base.getSndDelflg();
					fields12[10] = base.getStime();
					fields12[11] = DatetimeUtil.getDatetime();
					fields12[12] = base.getSeqID();
					
					sql = servSql.getUpdateSql();
					
					boolean  res = bSqlServ.dbWrite(cn, sql, fields12);
					if(!res){
						common.setAppFailMsg(msg , " 更新记录失败，请检查！");
						DbcpPool.rollBackTran(cn);
						DbcpPool.closeTran(cn);
						DbcpPool.returnConnection(cn);
						return list;
					}					
				}
			}
			
			DbcpPool.commitTran(cn);
			DbcpPool.closeTran(cn);
		    DbcpPool.returnConnection(cn);
		    
		} catch(Exception ex) 
		{
			/**
			 * 记录相关日志
			 */
			logger.error(ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
			
		} finally {
			DbcpPool.returnConnection(cn);
		}
		return list;
	}


	/**
	 * 添加私信到表中
	 * @see net.IUserbasicService.module.other.IUserService#add(net.swift.common.ReturnMessage)
	 */
	public void add(MessageForm theForm,MsgcontForm msgcontForm, ReturnMessage msg) 
	{
		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			
			DbcpPool.beginTran(cn);
			
			add( cn, theForm, msgcontForm,  msg);			

			DbcpPool.commitTran(cn);
			DbcpPool.closeTran(cn);			
			DbcpPool.returnConnection(cn);
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about invite" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
		return ;
	}
	/**
	 * 添加站内邮件
	 * @see net.IUserbasicService.module.other.IUserService#add(net.swift.common.ReturnMessage)
	 */
	public void add(Connection cn,MessageForm theForm, MsgcontForm msgcontForm, ReturnMessage msg) 
	{
		try
		{
			
			//检查隐私设置
			UserSetServiceImpl usersetservice = new UserSetServiceImpl();
			UserSetForm usersetForm = new UserSetForm();
			
			usersetForm.setUserID(theForm.getReceiver());
			
			usersetservice.get(cn, usersetForm, msg);
			if(msg.getErrCode() != 0){
				return;
			}
			//如果设置为只接收好友邮件 则判断发送者是否是好友
			if(usersetForm.getEmailFlg().equals(GlbFinal.USERSET_FRIEND)){
				
				FriendServiceImpl friendservice = new FriendServiceImpl();
				
				if(!friendservice.isFriend(cn, theForm.getReceiver(), theForm.getSender())){
					common.setAppFailMsg(msg , " 该会员设置了只接收好友邮件");
					DbcpPool.rollBackTran(cn);
					DbcpPool.closeTran(cn);
					DbcpPool.returnConnection(cn);
					return;					
				}
			}

			//保存消息内容
			MsgcontServiceImpl contservice = new MsgcontServiceImpl();

			contservice.add(cn, msgcontForm, msg);
			if(msg.getErrCode() != 0){
				
				return;
			}
			
			//消息发送者等信息
			Object[] fields = new Object[13];
			
			fields[0] = SequenceMgr.generateSeq(GlbFinal.SEQ_INITDATE, GlbFinal.SEQ_MESSAGE);
			fields[1] = theForm.getReceiver();
			fields[2] = theForm.getSender();
			fields[3] = theForm.getSendertype();
			fields[4] = msgcontForm.getMsgID();
			fields[5] = theForm.getMtype();
			fields[6] = theForm.getTitle();
			fields[7] = GlbFinal.WEBMSG_UNREAD;
			fields[8] = theForm.getRtime();
			fields[9] = GlbFinal.DELMSG_NO;
			fields[10] = GlbFinal.DELMSG_NO;
			fields[11] = DatetimeUtil.getDatetime();
			fields[12] = DatetimeUtil.getDatetime();
			
			MessageServiceSqlMysql servSql = new MessageServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();
			String sql = servSql.getInsertSql();
						
			boolean  res = bSqlServ.dbWrite(cn, sql, fields);
			if(!res){
				common.setAppFailMsg(msg , " 保存信息失败，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;
			}
			//更新邮件数量
			UserSysinfoServiceImpl syservice  = new UserSysinfoServiceImpl();
			
			UserSysinfoForm sysForm = new UserSysinfoForm();
			sysForm.setUserID(theForm.getReceiver());

			if(theForm.getMtype().equals(GlbFinal.MTYPE_SIXIN)){
				sysForm.setNemailNum(1);

			}else if(theForm.getMtype().equals(GlbFinal.MTYPE_ZHAOHU)){
				sysForm.setZhaohuNum(1);
			
			}else if(theForm.getMtype().equals(GlbFinal.MTYPE_QINGQIU)){
				sysForm.setInvitFrNum(1);
			
			}else if(theForm.getMtype().equals(GlbFinal.MTYPE_JIESHOU)){
				sysForm.setJyqingNum(1);
				
			}else if(theForm.getMtype().equals(GlbFinal.MTYPE_LIUYAN)){
				sysForm.setLiuyanNum(1);
				
			}else if(theForm.getMtype().equals(GlbFinal.MTYPE_PINGLUN)){
				sysForm.setPinlunNum(1);
				
			}else if(theForm.getMtype().equals(GlbFinal.MTYPE_HUIFU)){
				sysForm.setHuifuNum(1);
				
			}else if(theForm.getMtype().equals(GlbFinal.MTYPE_SHENQING)){
				sysForm.setCysqNum(1);
				
			}
			syservice.update(cn, sysForm, msg);
			if(msg.getErrCode() != 0){
				return;
			}
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about invite" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
		} 
		
		return ;
	}
	
	/**
	 * 删除邮件 一次最多删除20条邮件
	 * @see net.IUserbasicService.module.other.IUserService#update(net.swift.common.ReturnMessage)
	 */
	public void delete(MessageForm theForm, String[] ids, ReturnMessage msg) 
	{
		
		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			
			DbcpPool.beginTran(cn);
			
			String sql = "";

			MessageServiceSqlMysql servSql = new MessageServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();
			Object[] fields = new Object[22];
			
			fields[0] = GlbFinal.DELMSG_YES;
			fields[1] = DatetimeUtil.getDatetime();
		
			int len = ids.length;
			for(int i=0; i<len; i++){
				
				fields[i+2] = ids[i];	
			}
			int unRednum = 0;
			
			//修改所有邮件的状态为删除
			if(theForm.getQryReceiver() > 0){
				
				sql = servSql.getUpdRecvDelFlgSql();
				//计算待删除的邮件中有多少封未读邮件
				Object[] fields21 = new Object[21];
				fields21[0] = GlbFinal.WEBMSG_UNREAD;
				for(int i=0; i<len; i++){
					
					fields21[i+1] = ids[i];	
				}
				String unredsql = servSql.getUnRedNumSql();
				unRednum = bSqlServ.dbCount(cn, unredsql, fields21);
				
			}else if(theForm.getQrySender() > 0 ){
				
				sql = servSql.getUpdSndDelFlgSql();
			}
			boolean  res = bSqlServ.dbWrite(cn, sql, fields);
			if(!res){
				common.setAppFailMsg(msg , " 删除邮件失败，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;
			}			
	
			for(int i=0; i<len; i++){
				
				fields[i] = ids[i];	
			}			
			fields[20] = GlbFinal.DELMSG_YES;
			fields[21] = GlbFinal.DELMSG_YES;			
			
			//如果接收用户删除标志和发送用户删除标志都为 删除 , 先删除邮件内容
			MsgcontServiceSqlMysql contservSql = new MsgcontServiceSqlMysql();
			
			sql = contservSql.getDelbyIDSql();
			
			res = bSqlServ.dbWrite(cn, sql, fields);
			if(!res){
				common.setAppFailMsg(msg , " 删除邮件内容失败，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;
			}
			//如果接收用户删除标志和发送用户删除标志都为 删除  则删除该邮件
			sql = servSql.getDelbyIDSql();
								
			res = bSqlServ.dbWrite(cn, sql, fields);
			if(!res){
				common.setAppFailMsg(msg , " 删除邮件失败，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;
			}

			//更新 新邮件数量
			if(unRednum > 0){
				UserSysinfoServiceImpl syservice  = new UserSysinfoServiceImpl();
				
				UserSysinfoForm sysForm = new UserSysinfoForm();
				sysForm.setUserID(theForm.getQryReceiver());

				sysForm.setNemailNum(-1 * unRednum);
				syservice.update(cn, sysForm, msg);
				if(msg.getErrCode() != 0){
					return;
				}					
			}
			
			DbcpPool.commitTran(cn);
			DbcpPool.closeTran(cn);
			DbcpPool.returnConnection(cn);
			return;
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about invite" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
		return ;
	}

	/**
	 * 更新一条指定记录
	 * @see net.IUserbasicService.module.other.IUserService#update(net.swift.common.ReturnMessage)
	 */
	public void update(MessageForm theForm, ReturnMessage msg) 
	{
		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			
			DbcpPool.beginTran(cn);

			update(cn, theForm, msg);
			if(msg.getErrCode() != 0){
				return;
			}
			
			DbcpPool.commitTran(cn);
			DbcpPool.closeTran(cn);			
			DbcpPool.returnConnection(cn);
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about invite" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
		} finally {
			DbcpPool.returnConnection(cn);
		}
				
		return ;
	}
	/**
	 * 更新一条指定记录
	 * @see net.IUserbasicService.module.other.IUserService#update(net.swift.common.ReturnMessage)
	 */
	public void update(Connection cn, MessageForm theForm, ReturnMessage msg) 
	{
		try
		{

			MessageServiceSqlMysql servSql = new MessageServiceSqlMysql();
			
			String sql = servSql.getbykeySql();
		
			BasicSqlService  bSqlServ = new BasicSqlService();

			//取原记录的信息			
			Object[] fields2 = new Object[1];

			fields2[0] = theForm.getSeqID();
			
			List list = null;
		
			list = bSqlServ.dbRead(cn, sql, fields2, new DbMessageService());
			if(list.size() == 0){
				
				common.setAppFailMsg(msg , " 记录不存在，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;				
			}
			//填充到theForm的空字段中
						
			BaseMessage base = (BaseMessage)list.get(0);
			
			if(theForm.getSendertype().equals("")){
				
				theForm.setSendertype(base.getSendertype());
			}
			if(theForm.getMsgID() == 0){
				
				theForm.setMsgID(base.getMsgID());
			}
			if(theForm.getTitle().equals("")){
				
				theForm.setTitle(base.getTitle());
			}
			if(theForm.getStatus().equals("")){
				
				theForm.setStatus(base.getStatus());
			}
			if(theForm.getRtime().equals("")){
				
				theForm.setRtime(base.getRtime());
			}
			if(theForm.getRecvDelflg().equals("")){
				
				theForm.setRecvDelflg(base.getRecvDelflg());
			}
			if(theForm.getSndDelflg().equals("")){
				
				theForm.setSndDelflg(base.getSndDelflg());
			}
			if(theForm.getStime().equals("")){
				
				theForm.setStime(base.getStime());
			}
			
			//取得FORM中传入的查询参数
			Object[] fields = new Object[13];
			
			fields[0] = theForm.getReceiver();
			fields[1] = theForm.getSender();
			fields[2] = theForm.getSendertype();
			fields[3] = theForm.getMsgID();
			fields[4] = theForm.getMtype();
			fields[5] = theForm.getTitle();
			fields[6] = theForm.getStatus();
			fields[7] = theForm.getRtime();
			fields[8] = theForm.getRecvDelflg();
			fields[9] = theForm.getSndDelflg();
			fields[10] = theForm.getStime();
			fields[11] = DatetimeUtil.getDatetime();
			fields[12] = theForm.getSeqID();
			
			sql = servSql.getUpdateSql();
			
			boolean  res = bSqlServ.dbWrite(cn, sql, fields);
			if(!res){
				common.setAppFailMsg(msg , " 更新记录失败，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;
			}
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about invite" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
		} finally {
		}
				
		return ;
	}	
	/**
	 * 更新一条指定记录
	 * @see net.IUserbasicService.module.other.IUserService#update(net.swift.common.ReturnMessage)
	 */
	public void updsts(Connection cn, MessageForm theForm, ReturnMessage msg) 
	{
		try
		{

			MessageServiceSqlMysql servSql = new MessageServiceSqlMysql();
			
			String sql = servSql.getbykeySql();
		
			BasicSqlService  bSqlServ = new BasicSqlService();

			//取原记录的信息			
			Object[] fields2 = new Object[1];

			fields2[0] = theForm.getSeqID();
			
			List list = null;
		
			list = bSqlServ.dbRead(cn, sql, fields2, new DbMessageService());
			if(list.size() == 0){
				
				common.setAppFailMsg(msg , " 记录不存在，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;				
			}
			//填充到theForm的空字段中
						
			BaseMessage base = (BaseMessage)list.get(0);
			
			if(theForm.getMsgID() == 0){
				
				theForm.setMsgID(base.getMsgID());
			}

			if(theForm.getStatus().equals("")){
				
				theForm.setStatus(base.getStatus());
			}
			if(theForm.getRtime().equals("")){
				
				theForm.setRtime(base.getRtime());
			}
			if(theForm.getRecvDelflg().equals("")){
				
				theForm.setRecvDelflg(base.getRecvDelflg());
			}
			if(theForm.getSndDelflg().equals("")){
				
				theForm.setSndDelflg(base.getSndDelflg());
			}

			
			//取得FORM中传入的查询参数
			Object[] fields = new Object[5];
			
			fields[0] = theForm.getStatus();
			fields[1] = theForm.getRtime();
			fields[2] = theForm.getRecvDelflg();
			fields[3] = theForm.getSndDelflg();
			fields[4] = theForm.getSeqID();
			
			sql = servSql.getUpdstsSql();
			
			boolean  res = bSqlServ.dbWrite(cn, sql, fields);
			if(!res){
				common.setAppFailMsg(msg , " 更新记录失败，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;
			}
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about invite" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
		} finally {
		}
				
		return ;
	}	
	/**
	 * 更新一条指定记录
	 * @see net.IUserbasicService.module.other.IUserService#update(net.swift.common.ReturnMessage)
	 */
	public void deal(MessageForm theForm, ReturnMessage msg) 
	{
		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			DbcpPool.beginTran(cn);

			MessageServiceSqlMysql servSql = new MessageServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();
			
			String sql = servSql.getbykeySql();

			//取原记录的信息			
			Object[] fields2 = new Object[1];

			fields2[0] = theForm.getSeqID();
			
			List list = bSqlServ.dbRead(cn, sql, fields2, new DbMessageService());
			if(list.size() == 0){
				
				common.setAppFailMsg(msg , " 记录不存在，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;				
			}
	
			//填充到theForm的空字段中
			BaseMessage base = (BaseMessage)list.get(0);
			BeanUtils.copyProperties(theForm, base);

			//取得FORM中传入的查询参数
			Object[] fields = new Object[13];
			
			fields[0] = base.getReceiver();
			fields[1] = base.getSender();
			fields[2] = base.getSendertype();
			fields[3] = base.getMsgID();
			fields[4] = base.getMtype();
			fields[5] = base.getTitle();
			fields[6] = GlbFinal.WEBMSG_READ;
			fields[7] = base.getRtime();
			fields[8] = base.getRecvDelflg();
			fields[9] = base.getSndDelflg();
			fields[10] = base.getStime();
			fields[11] = DatetimeUtil.getDatetime();
			fields[12] = theForm.getSeqID();
			
			sql = servSql.getUpdateSql();
			
			boolean  res = bSqlServ.dbWrite(cn, sql, fields);
			if(!res){
				common.setAppFailMsg(msg , " 更新记录失败，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;
			}
			//如果是好友请求，则按同意处理
			if(base.getMtype().equals(GlbFinal.MTYPE_QINGQIU)){
				
				FriendServiceImpl friendservice = new FriendServiceImpl();
				FriendForm friendForm = new FriendForm();
				
				friendForm.setFriendID(base.getSender());
				friendForm.setUserID(base.getReceiver());
				
				friendservice.add(cn, friendForm, msg);
				if(msg.getErrCode() != 0){
					return;
				}
			}else
			//如果是关注请求，则按同意处理
			if(base.getMtype().equals(GlbFinal.MTYPE_GUANGZHU)){
					
				LinjuServiceImpl linjuservice = new LinjuServiceImpl();
				LinjuForm linjuForm = new LinjuForm();
					
				linjuForm.setUserID(base.getReceiver());
				linjuForm.setLinjuID(base.getSender());
					
				linjuservice.add(cn, linjuForm, msg);
				if(msg.getErrCode() != 0){
					return;
				}
			}else
			//如果是验证通过，则按验证通过处理
			if(base.getMtype().equals(GlbFinal.MTYPE_YANZHENG)||
				base.getMtype().equals(GlbFinal.MTYPE_YWYANZH)||
				base.getMtype().equals(GlbFinal.MTYPE_WYYANZH)){
				
				ChecklogServiceImpl checkservice = new ChecklogServiceImpl();
				ChecklogForm checkform = new ChecklogForm();
				
				if(base.getMtype().equals(GlbFinal.MTYPE_YWYANZH)){

					checkform.setCheckerType(GlbFinal.CHECKTYPE_YEWEI);
				}else if(base.getMtype().equals(GlbFinal.MTYPE_WYYANZH)){

					checkform.setCheckerType(GlbFinal.CHECKTYPE_WUYE);
				}else{
					checkform.setCheckerType(GlbFinal.CHECKTYPE_USERS);
				}
				checkform.setCheckerID(base.getReceiver());
				checkform.setUserID(base.getSender());
				
				checkservice.add(cn, checkform, msg);
				if(msg.getErrCode() != 0){
					return;
				}
			}
			
			DbcpPool.commitTran(cn);
			DbcpPool.closeTran(cn);			
			DbcpPool.returnConnection(cn);
			
			this.delete(theForm, new String[]{String.valueOf(theForm.getSeqID())}, msg);
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about invite" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
				
		return ;
	}
	
	public int getMsgNum(String userId) {
		 String sql = "select count(*) as COUNT from Messages where  receiver=" + userId + " and recvDelflg=1 and Status=1 and Mtype=1 and Sendertype=1";
		 
		 int num = 0;
		 Connection cn = null;
			try
			{
				cn = DbcpPool.getConnection();
				
				ResultSet rs = cn.prepareStatement(sql).executeQuery();
				while(rs.next()) {
					num = rs.getInt(1);
				}
				
				
			} catch(Exception ex) 
			{
				logger.error("System error , param get about invite" + ex);
				try 
				{
					DbcpPool.returnConnection(cn);
				} catch(Exception ex1) 
				{
					ex1.printStackTrace();
					
				}			
			} finally {
				DbcpPool.returnConnection(cn);
			}
			return num;
	}
}
